/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.ControlEscolar.DAOs;


import com.ControlEscolar.Beans.Exam;
import java.util.ArrayList;


/**
 *
 * @author Bere
 */

public class ExamDAO extends AbstractDAO <Exam> {

    @Override
    public void add(Exam exam) throws DAOException {
                String sql = "INSERT INTO exams (examGrade,examType,subjectKey,studentKey,ID_Professor,dates,ID_Request, ID_Curriculum)values("
               +exam.getExamGrade()+",\""
               +exam.getExamType()+"\",\""
               +exam.getSubjectKey()+"\",\""
               +exam.getStudentKey()+"\","
               +exam.getID_Professor()+",\""
               +exam.getDates()+"\","
               +exam.getID_Request()+","
               +exam.getID_Curriculum()+")";


        try {
            connection.executeUpdate(sql);
        } catch (Exception ex) {
            throw new DAOException();
        }
    }

    @Override
    public int delete(String condition) throws DAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int update(Exam exam) throws DAOException {
        try {
            String sql = "UPDATE exams SET examGrade= " + exam.getExamGrade()+" WHERE examType= \""
                    + exam.getExamType() + "\" and subjectKey=\"" + exam.getSubjectKey()
                    + "\" and studentKey=\"" + exam.getStudentKey() + "\" and ID_Professor="
                    + exam.getID_Professor() +" and ID_Request="+exam.getID_Request()+ " and ID_Curriculum="
                    + exam.getID_Curriculum();
            System.out.println(sql);
            connection.executeUpdate(sql);
        } catch (Exception ex) {
            throw new DAOException();
        }
        return 0;
    }

    @Override
    public ArrayList<Exam> query () throws DAOException {
         try {
            String sql = "SELECT * FROM exams";
            return connection.executeQuery(sql, Exam.class);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw new DAOException();

        }
    }

     public ArrayList<Exam> queryFailedExtras(String subjectKey,String studentKey) throws DAOException{
        try {
            String sql = "SELECT * FROM exams where  subjectKey= \""+subjectKey
                    +"\" and studentKey=\""+studentKey+"\" " +
                    "and examType= \"EX\" and examGrade<60 ";

           return connection.executeQuery(sql, Exam.class);
        } catch (Exception ex) {
            throw new DAOException();
        }
     }

     public Exam queryExam(Exam exam) throws DAOException{
        try {
           String sql = "SELECT * FROM exams where examType= \"" + exam.getExamType()
                   + "\" and subjectKey= \"" + exam.getSubjectKey() +
                   "\" and studentKey= \"" + exam.getStudentKey() +
                   "\" and ID_Professor=" + exam.getID_Professor()+
                   " and ID_Curriculum="+exam.getID_Curriculum();

            ArrayList<Exam>  examQuery= connection.executeQuery(sql, Exam.class);
         return getSpecificQuery(examQuery);
        } catch (Exception ex) {
            throw new DAOException();
        }
     }

        public  ArrayList<Exam> queryExtras(String subjectkey,String studentKey) throws DAOException{
        try {
            String sql = "SELECT * FROM exams where  subjectKey= \"" +subjectkey + 
                    "\" and studentKey=\"" +studentKey +
                    "\"  and examType=\"EX\"";
            return connection.executeQuery(sql, Exam.class);

        } catch (Exception ex)
        {

            throw new DAOException();
        }
    }

    public  ArrayList<Exam> queryOrdinary(String studentKey,String subjectKey) throws DAOException{
        try {
            String sql = "SELECT * FROM exams where  subjectKey= \"" +subjectKey +
                    "\" and studentKey=\"" +studentKey +
                    "\" and examType=\"OR\" ";
            return connection.executeQuery(sql, Exam.class);
        } catch (Exception ex)
        {
            throw new DAOException();
        }
    }

       public Exam queryLastExam(String subjectKey,String studentKey) throws DAOException{
        try {
            String sql = "SELECT * FROM exams where subjectKey=\""+subjectKey+
                    "\" and studentKey=\""+studentKey+
                    "\" and dates=(select max(dates) from exams where subjectKey=\""+subjectKey+
                    "\" and studentKey=\""+studentKey+"\")";
            ArrayList<Exam> gradeQuery= connection.executeQuery(sql, Exam.class);
          return getSpecificQuery(gradeQuery);
        } catch (Exception ex) {
            throw new DAOException();
        }
    }

     public ArrayList<Exam> queryLastExams(String studentKey) throws DAOException{
        try {
           String sql = "SELECT id_exam, examgrade,examtype,subjectkey, studentkey,id_professor, id_request and studentkey=\""
            + studentKey + "\", max(dates)  as dates  FROM exams group by subjectkey";
            return connection.executeQuery(sql, Exam.class);
        } catch (Exception ex) {
            throw new DAOException();
        }
         
     }

     //no se usa por el momento
     public ArrayList<Exam> queryStudentExams(String studentKey) throws DAOException{
        try {
            String sql = "SELECT * FROM exams WHERE studentkey= \"" + studentKey + "\"";
             return connection.executeQuery(sql, Exam.class);
        } catch (Exception ex) {
            throw new DAOException();
        }
     }

    public Exam queryByID(String ID_Exam) {
        try {
            String sql = "SELECT * FROM exams where ID_Exam=" + ID_Exam;
            
            ArrayList<Exam> examQuery = connection.executeQuery(sql, Exam.class);
            return getSpecificQuery(examQuery);
        } catch (Exception ex) {
            return null;
        }
    }

}
